﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.OleDb;

public partial class OrderLookup : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        lblOrderData.Text = "";
        Page.Form.DefaultButton = cmdLookup.UniqueID;
        if (Page.IsPostBack && !txtOrderLookup.Text.Equals(""))
        {
            Lookup();
        }
    }

    private void Lookup()
    {
        OleDbConnection objConnection;
        OleDbDataAdapter objCommand;
        String strConnect;
        String strCommand;
        DataTable dt;

        strConnect = @"Provider=Microsoft.Jet.OLEDB.4.0;";
        strConnect += @"Data Source=" + Server.MapPath("App_Data/db.mdb") + ";";
        strConnect += @"Persist Security Info=False";

        strCommand = "SELECT * from Orders where OrderID=" + txtOrderLookup.Text;

        objConnection = new OleDbConnection(strConnect);
        objCommand = new OleDbDataAdapter(strCommand, objConnection);
        dt = new DataTable();
        objCommand.Fill(dt);

        if(dt.Rows.Count == 0)
            lblOrderData.Text = "No matching record found...";
        else
        {
            lblOrderData.Text += "<table cellpadding=\"3\" cellspacing=\"2\">";
            lblOrderData.Text += "<tr><td>User Name:</td><td>" + dt.Rows[0]["UserName"] + "</td>";
            lblOrderData.Text += "<tr><td>Name:</td><td>" + dt.Rows[0]["FirstName"] + " " + dt.Rows[0]["LastName"]+"</td>";
            lblOrderData.Text += "<tr><td>Street Address:</td><td>" + dt.Rows[0]["Address"] + " <br>" + dt.Rows[0]["Address2"] + "</td>";
            lblOrderData.Text += "<tr><td>City:</td><td>" + dt.Rows[0]["City"] + "</td>";
            lblOrderData.Text += "<tr><td>State:</td><td>" + dt.Rows[0]["State"] + "</td>";
            lblOrderData.Text += "<tr><td>Zip:</td><td>" + dt.Rows[0]["Zip"] + "</td>";
            lblOrderData.Text += "<tr><td>Transaction Date:</td><td>" + Convert.ToDateTime(dt.Rows[0]["TransactionDate"]).ToString("D") + "</td>";
            lblOrderData.Text += "</table>";
            lblOrderData.Text += "<br/>";

            strCommand = "SELECT Books.Title, OrderItems.Quantity, OrderItems.Price from Books, OrderItems where OrderItems.OrderID=" + txtOrderLookup.Text + " and OrderItems.ISBN = Books.ISBN";
            objCommand = new OleDbDataAdapter(strCommand, objConnection);
            dt = new DataTable();
            objCommand.Fill(dt);

            decimal Count = 0;
            if (dt.Rows.Count != 0)
            {
                lblOrderData.Text += "<table cellpadding=\"5\" cellspacing=\"0\" border=\"1\" class=\"table\" style=\"width:100%;border-collapse: collapse;\">";
                lblOrderData.Text += "<tr style=\"background-color: rgb(159, 137, 182);\"><td><b><center>Title</b></center></td><td><b><center>Quantity</b></center></td><td><b><center>Price</b></center></td>";
                foreach (DataRow dr in dt.Rows)
                {
                    lblOrderData.Text += "<tr><td>" + dr["Title"] + "</td><td>" + dr["Quantity"] + "</td><td>" + String.Format("{0:C}",Convert.ToDecimal(dr["Price"])) + "</td>";
                    Count += Convert.ToDecimal(dr["Price"]) * Convert.ToDecimal(dr["Quantity"]);
                }
                lblOrderData.Text += "</table>";
                lblOrderData.Text += String.Format("Total Amount : {0:C}",Count);
            }
        }
    }
}
